package guru.mercurio.eps.sWAN;

import guru.mercurio.common.PhoneAddress;
import guru.merlin.core.AbstractEnvelopeProcessor;
import guru.merlin.core.ConfigurationCantHandException;
import guru.merlin.core.CantHandException;
import guru.merlin.core.EnvelopeCantHandException;
import guru.merlin.core.InfrastructureCantHandException;
import guru.merlin.core.PeerCantHandException;
import guru.merlin.core.Envelope;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
//import java.sql.ResultSet;
import java.sql.SQLException;
//import java.sql.Statement;
import java.util.Properties;

import org.apache.log4j.Logger;

public class StoreProcedureEP extends AbstractEnvelopeProcessor{

    //private int loginTimeout;
    private Connection conn = null;
    private String driver = null;
    private String userName = null;
    private String password = null;
    private String url = null;
    private static Logger logger = Logger.getLogger(StoreProcedureEP.class);
    Properties props = new Properties();
    //private String parametersFilePath;
    //private String lastCel;

    public StoreProcedureEP() {
        super();
    }

    public void closeConnection() throws CantHandException {
        try {
            if (conn != null) conn.close();
        } catch (SQLException sqle) {
            throw new InfrastructureCantHandException(sqle.getMessage());
        }
    }

    public void openConnection(String parametersFilePath) throws CantHandException {
        try {
            try {
                InputStream fis = new FileInputStream(parametersFilePath);
                props.load(fis);
                fis.close();
                logger.info("Properties file loaded");
            } catch (IOException e) {
                e.printStackTrace();
                logger.error("", e);
                throw new ConfigurationCantHandException("Properties file can't be loaded");
            }
            this.conn = getConnection();
        } catch (PeerCantHandException epe) {
            throw new PeerCantHandException(epe.toString());
        }
    }

    public void start() throws CantHandException {
        try {
            if (conn == null) {
                this.conn = getConnection();
            }
        } catch (PeerCantHandException e) {
            e.printStackTrace();
            logger.error("There is an infrastructure problem impossible to initiate the Node.");
            throw new PeerCantHandException("There is an infrastructure problem impossible to initiate the Node. "+e.getMessage());
        }
    }

    private Connection getConnection() throws CantHandException {
        try {
            driver = props.getProperty("driver");
            Class.forName(driver.trim()).newInstance();
            url = props.getProperty("url").trim();
            userName = props.getProperty("userName").trim();
            password = props.getProperty("password").trim();
            //loginTimeout = Integer.parseInt(props.getProperty("login.timeout", "20"));
            try {
                //logger.info("Opening data base connection");
                conn = DriverManager.getConnection(url, userName, password);
                logger.info("Open data base connection Sucessful");
                if (conn == null) {
                    logger.error("Data Base Connection is null");
                    throw new PeerCantHandException("Data Base Connection is null");
                }

            } catch (SQLException sqle) {
                sqle.printStackTrace();
                throw new PeerCantHandException(sqle.toString());
            }
        } catch (IllegalAccessException iae) {
            iae.printStackTrace();
            throw new PeerCantHandException(iae.toString());
        } catch (InstantiationException ie) {
            ie.printStackTrace();
            throw new PeerCantHandException(ie.toString());
        } catch (ClassNotFoundException cnfe) {
            cnfe.printStackTrace();
            throw new PeerCantHandException(cnfe.toString());
        }

        return conn;
    }

    public void callStoreProcedure(String message, String destAddress) throws Exception {
        String logmsg = "";
        String login = (props.getProperty("login").trim() == "") ? "twindivision" : props.getProperty("login").trim();
        //String idd = (props.getProperty("idd").trim() == "") ? "@i" : props.getProperty("idd").trim();
        //String cola = (props.getProperty("cola").trim() == "") ? "@c" : props.getProperty("cola").trim();
        //String msg = (props.getProperty("msg").trim() == "") ? "@m" : props.getProperty("msg").trim();
        String storeProcedure = "{CALL agrega_mensaje(?,?,?,?,?,?)}";
        CallableStatement callStmt = null;	 
        try {
            if (conn == null) { 
                try {
                    getConnection();
                } catch(Exception che){
                    throw new PeerCantHandException("Data Base Connection is null");
                }
            } 
            logger.info("Message "+message +" destAddress"+ destAddress);
            try {
                callStmt = conn.prepareCall(storeProcedure);
                callStmt.setString(1, message);
                callStmt.setString(2, destAddress);
                callStmt.setString(3, login);
                callStmt.registerOutParameter(4,java.sql.Types.VARCHAR);
                callStmt.registerOutParameter(5,java.sql.Types.VARCHAR);
                callStmt.registerOutParameter(6,java.sql.Types.VARCHAR);
                //callStmt.setString(4, idd);
                //callStmt.setString(5, cola);
                //callStmt.setString(6, msg);

                logger.info("The stored procedure is going to be called");
                //callStmt.execute();
                callStmt.executeUpdate();
            } catch (com.mysql.jdbc.MysqlDataTruncation sqle) {
                logger.error(sqle);
                throw new Exception(sqle.getMessage());
            }
            try {   
                String i = callStmt.getObject(4).toString();
                String c = callStmt.getObject(5).toString();
                String d = callStmt.getObject(6).toString();
                logmsg =" IDD: " +i+" COLA: " +c+" MSG: " + d; 
                logger.debug(logmsg);
                /*
                Statement stmt = null;
                stmt = conn.createStatement();
                ResultSet rs = null;
                if (stmt.execute("SELECT @IDD,@COLA,@salida")) {
                    if (stmt != null) rs = stmt.getResultSet();
                }
                if (rs != null) {
                    String i = "";
                    String c = "";
                    String d = "";
                    while (rs.next()) {
                        i = rs.getString(1);
                        c = rs.getString(2);
                        d = rs.getString(3);
                    }
                    if(logger.isDebugEnabled()) {
                        logmsg =" IDD: " +i+" COLA: " +c+" MSG: " + d; 
                        logger.debug(logmsg);
                    }  
                    rs.close();
                    rs = null;
                }
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }*/
            } catch (SQLException sqlEx) { // ignore
                logger.error("SQLException "+sqlEx);
            }
                
            this.closeConnection();
            logger.info("Call stored procedure Sucessful");
            logger.info("Connection Closed");
        } catch (Exception e) {
            logger.error(e);
            throw new Exception(e.getMessage());
        }
    }

    public Object handEnvelope(Envelope envelope) throws CantHandException {
        try {
            PhoneAddress destinationPhone;
            //PhoneAddress originPhone;
            String message = null;

            logger.info("Envelope received for dispatching:" + envelope.toStringNoContent());

            if (!(envelope.getDestination() instanceof PhoneAddress)) { throw new EnvelopeCantHandException("Destination is not a Phone Number"); }
            destinationPhone = (PhoneAddress) envelope.getDestination();

            String dest = null;

            if (destinationPhone.getAreaCode().equals("416")) dest = "m" + destinationPhone.getLocal();

            if (destinationPhone.getAreaCode().equals("414")) dest = "t" + destinationPhone.getLocal();

            if (destinationPhone.getAreaCode().equals("412")) dest = "d" + destinationPhone.getLocal();

            if (destinationPhone.getAreaCode().equals("417")) dest = "c" + destinationPhone.getLocal();

            if (destinationPhone.getAreaCode().equals("418")) dest = "i" + destinationPhone.getLocal();

            message = envelope.getContent().toString();

            this.callStoreProcedure(message, dest);
            logger.info("Store Procedure called");
        } catch (Exception che) {
            throw new PeerCantHandException(che.toString());
        }  finally{

        }
        return envelope;
    }

    public static Properties defaultLoggerProperties() {
        Properties properties = new Properties();
        properties.put("log4j.rootCategory", "info,console");
        properties.put("log4j.appender.console", "org.apache.log4j.ConsoleAppender");
        properties.put("log4j.appender.console.layout", "org.apache.log4j.PatternLayout");
        properties.put("log4j.appender.console.layout.ConversionPattern", "%d{dd MM yyyy HH:mm:ss,SSS} %5p %c - %m%n  ");
        return properties;
    }

    /*
     * (sin Javadoc)
     * 
     * @see guru.merlin.core.EnvelopeHandler#retrieveEnvelope(java.lang.Object)
     */
    public void retrieveEnvelope(Object EnvelopeReference) throws CantHandException {

    }

    /*
     * (sin Javadoc)
     * 
     * @see guru.merlin.core.EnvelopeHandler#commitHandEnvelope(java.lang.Object)
     */
    public void commitHandEnvelope(Object EnvelopeReference) throws CantHandException {

    }

}